﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DestinationOperations
{
    public class DAO
    {
        private static TravelAgencyDataContext cont;

        static DAO()
        {
            cont = new TravelAgencyDataContext();
        }

        public static void AddDestination(string destinationName)
        {
            Destination dest = new Destination();
            dest.DestistionName = destinationName;
            cont.Destinations.InsertOnSubmit(dest);
            cont.SubmitChanges();
        }

        //Adding WayStation in a new Destination. The WayStation is added at the end
        //of the WayStation`s list.
        public static void AddWayStation(long settlementID, long destinationID, int distanceTo,
            int timeTo, int stay, decimal priceTo, int orderNumber)
        {
            //int lastNumber = getLastOrderNumber(destinationID);

            WayStation wayStation = new WayStation();
            wayStation.SettlementID = settlementID;
            wayStation.DestinationID = destinationID;
            wayStation.WayStationDistanceTo = distanceTo;
            wayStation.WayStationTimeTo = timeTo;
            wayStation.WayStationStay = stay;
            wayStation.WayStationPriceTo = priceTo;
            wayStation.WayStationOrderNumber = orderNumber;
            cont.WayStations.InsertOnSubmit(wayStation);
            cont.SubmitChanges();
        }

        //private static int getLastOrderNumber(long destinationID)
        //{
        //    var lastNumber = (from d in cont.Destinations
        //                      join w in cont.WayStations
        //                      on d.DestinationID equals w.DestinationID
        //                      where d.DestinationID == destinationID
        //                      orderby w.WayStationOrderNumber ascending 
        //                      select w).First().WayStationOrderNumber;

        //    return lastNumber;
        //}

        //Inserting new WayStation in existing Destination. Assigning new values for the WayStation before
        //which the new WayStation is added(newDistanceTo, newTimeTo, newStay, newPriceTo). 
        public static void InsertWayStation(long settlementID, long destinationID, int distanceTo,
            int timeTo, int stay, decimal priceTo, long addAfterWayStationID,
             int newDistanceTo, int newTimeTo, decimal newPriceTo)
        {
            int orderNumber = GetWayStationByID(addAfterWayStationID).WayStationOrderNumber;
            List<WayStation> wayList = GetAllWayStationsToEnd(orderNumber, destinationID);
            WayStation newWayStation = new WayStation();

            newWayStation.SettlementID = settlementID;
            newWayStation.DestinationID = destinationID;
            newWayStation.WayStationDistanceTo = distanceTo;
            newWayStation.WayStationTimeTo = timeTo;
            newWayStation.WayStationStay = stay;
            newWayStation.WayStationPriceTo = priceTo;
            newWayStation.WayStationOrderNumber = (++orderNumber);
            cont.WayStations.InsertOnSubmit(newWayStation);

            foreach (WayStation way in wayList)
            {
                way.WayStationOrderNumber++;
            }

            wayList.First().WayStationDistanceTo = newDistanceTo;
            wayList.First().WayStationTimeTo = newTimeTo;
            //wayList.First().WayStationStay = newStay;
            wayList.First().WayStationPriceTo = newPriceTo;

            cont.SubmitChanges();
        }

        public static void AddSettlement(string name)
        {
            Settlement settlement = new Settlement();
            settlement.SettlementName = name;
            cont.Settlements.InsertOnSubmit(settlement);
            cont.SubmitChanges();
        }

        public static void AddStartHour(StartHour startHour)
        {
            cont.StartHours.InsertOnSubmit(startHour);
            cont.SubmitChanges();
        }

        public static List<int> GetAllStartHoursForDestination(long destID)
        {
            var satrtHours = from s in cont.StartHours
                             join d in cont.Destinations
                             on s.DestinationID equals d.DestinationID
                             where destID == d.DestinationID
                             select s.StartHourValue;

            return satrtHours.ToList<int>();
        }

        private static List<WayStation> GetAllWayStationsToEnd(int orderNumber, long destinationID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumber & d.DestinationID == destinationID
                           orderby w.WayStationOrderNumber
                           select w;

            return allToEnd.ToList();
        }

        public static List<string> GetWayStationsToEnd(int orderNumber, long destinationID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumber && d.DestinationID == destinationID
                           orderby w.WayStationOrderNumber
                           select w.Settlement.SettlementName;

            return allToEnd.ToList();
        }

        public static WayStation GetTheLastWayStationForDestination(long destID)
        {
            var allToEnd = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           orderby w.WayStationOrderNumber descending
                           select w;

            return allToEnd.First();
        }

        public static List<WayStation> GetAllWayStationForDestination(long destinationID)
        {
            var allWayStation = from w in cont.WayStations
                                join d in cont.Destinations
                                on w.DestinationID equals d.DestinationID
                                where w.DestinationID == destinationID
                                orderby w.WayStationOrderNumber
                                select w;

            return allWayStation.ToList();
        }

        public static void DeleteDestination(long destinationID)
        {
            var destination = (from d in cont.Destinations
                               where d.DestinationID == destinationID
                               select d).First();

            cont.Destinations.DeleteOnSubmit(destination);
            cont.SubmitChanges();
        }

        public static void DeleteBus(long busID)
        {
            var bus = (from b in cont.Buses
                       where b.BusID == busID
                       select b).First();

            cont.Buses.DeleteOnSubmit(bus);
            cont.SubmitChanges();
        }

        //Deleating a WayStation. Assigning new values for the WayStation before
        //which the WayStation is deleated(newDistanceTo, newTimeTo, newStay, newPriceTo).
        public static void DeleteWayStation(long wayStationID,
            int newDistanceTo, int newTimeTo, int newStay, decimal newPriceTo)
        {
            long destinationID = GetWayStationByID(wayStationID).DestinationID;
            int orderNumber = GetWayStationByID(wayStationID).WayStationOrderNumber;

            List<WayStation> wayList = GetAllWayStationsToEnd(orderNumber, destinationID);

            foreach (WayStation way in wayList)
            {
                way.WayStationOrderNumber--;
            }

            wayList.First().WayStationDistanceTo = newDistanceTo;
            wayList.First().WayStationTimeTo = newTimeTo;
            wayList.First().WayStationStay = newStay;
            wayList.First().WayStationPriceTo = newPriceTo;

            cont.WayStations.DeleteOnSubmit(GetWayStationByID(wayStationID));
            cont.SubmitChanges();
        }

        private static WayStation GetWayStationByID(long wayStationID)
        {
            var wayStation = (from w in cont.WayStations
                              where w.WayStationID == wayStationID
                              select w).First();

            return wayStation;
        }

        public static List<Settlement> GetAllSettlements()
        {
            var allSettlements = from settlements in cont.Settlements
                                 select settlements;

            return allSettlements.ToList<Settlement>();
        }

        public static List<Destination> GetAllDestinations()
        {
            var getDestinations = from dest in cont.Destinations
                                  select dest;

            return getDestinations.ToList();
        }

        public static void AddScheduleType(string schedule)
        {
            SheduleType scheduleType = new SheduleType();
            scheduleType.SheduleDayOfWeek = schedule;
            cont.SheduleTypes.InsertOnSubmit(scheduleType);
            cont.SubmitChanges();
        }

        public static long GetScheduleTypeID(string schedule)
        {
            var result = from s in cont.SheduleTypes
                         where s.SheduleDayOfWeek == schedule
                         select s.SheduleTypeID;

            return result.First();
        }

        //public static long GetScheduleTypeIDbyDestID(long id)
        //{
        //    var result = from s in cont.SheduleTypes
        //                 where s.SheduleDayOfWeek == schedule
        //                 select s.SheduleTypeID;

        //    return result.First();
        //}

        public static long GetSettlementID(string settlementName)
        {
            var result = from s in cont.Settlements
                         where s.SettlementName == settlementName
                         select s.SettlementID;

            return result.First();
        }

        public static void AddNewBus(Buse bus)
        {
            cont.Buses.InsertOnSubmit(bus);
            cont.SubmitChanges();
        }

        public static List<Buse> GetAllBuses()
        {
            var buses = from b in cont.Buses
                        select b;

            return buses.ToList();
        }

        public static long GetWeekScheduleForDestination(long destID)
        {
            var schedule = from d in cont.Destinations
                           join s in cont.StartHours
                           on d.DestinationID equals s.DestinationID
                           where s.DestinationID == destID
                           select s.SheduleTypeID;

            return schedule.First();
        }

        public static void AttachBus(BusShedule busSchedule)
        {
            cont.BusShedules.InsertOnSubmit(busSchedule);
            cont.SubmitChanges();
        }

        public static List<StartHour> GetAllStartHoursForDest(long destID)
        {
            var startHours = from s in cont.StartHours
                             where s.DestinationID == destID
                             select s;

            return startHours.ToList();
        }

        public static int GetWayStationTimesFromBeginning(long destID, int orderNumber)
        {
            int time = 0;
            int stay = 0;

            var allTimesFromBeginning = from w in cont.WayStations
                                        join d in cont.Destinations
                                        on w.DestinationID equals d.DestinationID
                                        where w.WayStationOrderNumber <= orderNumber && d.DestinationID == destID
                                        select w;

            var allStaysFromBeginning = from w in cont.WayStations
                                        join d in cont.Destinations
                                        on w.DestinationID equals d.DestinationID
                                        where w.WayStationOrderNumber < orderNumber && d.DestinationID == destID
                                        select w;

            foreach (var t in allTimesFromBeginning)
            {
                time += t.WayStationTimeTo;
            }

            foreach (var s in allStaysFromBeginning)
            {
                stay += s.WayStationStay;
            }

            return time + stay;
        }

        public static int GetTimeBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            int time = 0;
            int stay = 0;

            var allTimes = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                               && d.DestinationID == destID
                           select w;

            var allStays = from w in cont.WayStations
                           join d in cont.Destinations
                           on w.DestinationID equals d.DestinationID
                           where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber < orderNumberTo
                               && d.DestinationID == destID
                           select w;

            foreach (var t in allTimes)
            {
                time += t.WayStationTimeTo;
            }

            foreach (var s in allStays)
            {
                stay += s.WayStationStay;
            }

            return time + stay;
        }

        public static int GetDistancesBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            int distance = 0;

            var allDistances = from w in cont.WayStations
                               join d in cont.Destinations
                               on w.DestinationID equals d.DestinationID
                               where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                                   && d.DestinationID == destID
                               select w;

            foreach (var d in allDistances)
            {
                distance += d.WayStationDistanceTo;
            }

            return distance;
        }

        public static decimal GetPricesBetweenConcreteStations(long destID, int orderNumberFrom, int orderNumberTo)
        {
            decimal price = 0;

            var allPrices = from w in cont.WayStations
                            join d in cont.Destinations
                            on w.DestinationID equals d.DestinationID
                            where w.WayStationOrderNumber > orderNumberFrom && w.WayStationOrderNumber <= orderNumberTo
                                && d.DestinationID == destID
                            select w;

            foreach (var p in allPrices)
            {
                price += p.WayStationPriceTo;
            }

            return price;
        }

        public static void RemoveStartHour(string startHour)
        {
            int result = GetStartHourValue(startHour);

            var shID = from s in cont.StartHours
                       where s.StartHourValue == result
                       select s;

            cont.StartHours.DeleteOnSubmit(shID.First());
            cont.SubmitChanges();
        }

        public static bool IsStartHourExists(string startHour)
        {
            int result = GetStartHourValue(startHour);

            try
            {
                var sh = (from s in cont.StartHours
                          where s.StartHourValue == result
                          select s).First();

                return true;
            }
            catch(InvalidOperationException)
            {
                return false;
            }
        }

        private static int GetStartHourValue(string startHour)
        {
            string[] time = startHour.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
            return (Convert.ToInt32(time[0]) * 60) + Convert.ToInt32(time[1]);
        }

        public static void Update()
        {
            cont.SubmitChanges();
        }

    }
}

